﻿namespace CRM.DataAccess.DAOs
{
    using System.Collections.Generic;
    using System.Data.Entity;
    using CRM.DataAccess.Entities;
    using System.Linq;
    using System;

    public class QuoteDAO : DbContext
    {
        public QuoteDAO()
            : base("name=Entities")
        {
        }
        public DbSet<QUOTE_STAGE> QUOTE_STAGE { get; set; }
        public DbSet<QUOTE> QUOTES { get; set; }
        public DbSet<QUOTE_DETAIL> QUOTE_DETAILS { get; set; }
        public DbSet<ITEM_DETAIL> ITEM_DETAILS { get; set; }
        public DbSet<ITEM> ITEMS { get; set; }

        //Delete Quote
        public int DeleteQuote(long quoteID)
        {
            try
            {
                QUOTES.RemoveRange(QUOTES.Where(x => x.ID == quoteID));
                this.SaveChanges();
                return 1;
            }
            catch
            {
                return -1;
            }
        }
        //get  Invoice by ID
        public QUOTE GetQuoteByID(long ID)
        {
            try
            {
                if (ID != null)
                {
                    var Query = (from q in QUOTES
                                 where q.ID == ID
                                 select q).FirstOrDefault<QUOTE>();
                    QUOTE quote = Query as QUOTE;
                    return quote;
                }
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get quote detail by QUOTE_ID
        public QUOTE_DETAIL GetQuoteDetailByID(long QuoteID)
        {
            try
            {
                var Query = (from q in QUOTE_DETAILS
                            where q.QuoteID == QuoteID
                            select q);
                List<QUOTE_DETAIL> quotes = Query.AsNoTracking().ToList();
                if (quotes != null)
                    return quotes[0];
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get quote by condition
        public List<QUOTE_DETAIL> GetQuoteByCondition(DateTime? dateFrom, DateTime? dateTo, string subject, long? quoteStage_ID, List<long?> ListConID, 
            List<long?> ListOrgID, long? assignUser_ID)
        {
            try
            {
                List<QUOTE_DETAIL> sum = new List<QUOTE_DETAIL>();
                if (ListOrgID.Count > 0)
                {
                    if (ListConID.Count > 0)
                    {
                        foreach (long? OrgID in ListOrgID)
                        {
                            foreach (long? ContactID in ListConID)
                            {
                                var Query = from q in QUOTE_DETAILS
                                            where  (subject != "" ? (q.SUBJECT.ToUpper().Contains(subject.ToUpper())) : (true))
                                           // q.SUBJECT.ToUpper() == (subject == "" ? q.SUBJECT.ToUpper() : subject.ToUpper())
                                                && q.QUOTE_STAGE_ID == (quoteStage_ID == null ? q.QUOTE_STAGE_ID : quoteStage_ID)
                                                && q.CONTACT_ID == (ContactID == null ? q.CONTACT_ID : ContactID)
                                                && q.OrgID == ( OrgID == null ? q.OrgID : OrgID)
                                                && q.ASSIGN_TO_ID == (assignUser_ID == null ? q.ASSIGN_TO_ID : assignUser_ID)
                                                && (q.DATE_CREATED >= dateFrom && q.DATE_CREATED < dateTo)
                                            select q;
                                List<QUOTE_DETAIL> orgs = Query.AsNoTracking().ToList();
                                sum.AddRange(orgs);
                            }
                        }
                    }
                    else
                    {
                        foreach (long? OrgID in ListOrgID)
                        {
                            var Query = from q in QUOTE_DETAILS
                                        where (subject != "" ? (q.SUBJECT.ToUpper().Contains(subject.ToUpper())) : (true))
                                        //q.SUBJECT.ToUpper() == (subject == "" ? q.SUBJECT.ToUpper() : subject.ToUpper())
                                            && q.QUOTE_STAGE_ID == (quoteStage_ID == null ? q.QUOTE_STAGE_ID : quoteStage_ID)
                                            && q.OrgID == (OrgID == null ? q.OrgID : OrgID)
                                            && q.ASSIGN_TO_ID == (assignUser_ID == null ? q.ASSIGN_TO_ID : assignUser_ID)
                                            && (q.DATE_CREATED >= dateFrom && q.DATE_CREATED < dateTo)
                                        select q;
                            List<QUOTE_DETAIL> orgs = Query.AsNoTracking().ToList();
                            sum.AddRange(orgs);
                        }
                    }

                }
                else
                {
                    if (ListConID.Count > 0)
                    {
                        foreach (long? ContactID in ListConID)
                        {
                            var Query = from q in QUOTE_DETAILS
                                        where (subject != "" ? (q.SUBJECT.ToUpper().Contains(subject.ToUpper())) : (true))
                                        //q.SUBJECT.ToUpper() == (subject == "" ? q.SUBJECT.ToUpper() : subject.ToUpper())
                                            && q.QUOTE_STAGE_ID == (quoteStage_ID == null ? q.QUOTE_STAGE_ID : quoteStage_ID)
                                            && q.CONTACT_ID == (ContactID == null ? q.CONTACT_ID : ContactID)
                                            && q.ASSIGN_TO_ID == (assignUser_ID == null ? q.ASSIGN_TO_ID : assignUser_ID)
                                            && (q.DATE_CREATED >= dateFrom && q.DATE_CREATED < dateTo)
                                        select q;
                            List<QUOTE_DETAIL> orgs = Query.AsNoTracking().ToList();
                            sum.AddRange(orgs);
                        }
                    }
                    else
                    {
                        var Query = from q in QUOTE_DETAILS
                                    where (subject != "" ? (q.SUBJECT.ToUpper().Contains(subject.ToUpper())) : (true))
                                    //q.SUBJECT.ToUpper() == (subject == "" ? q.SUBJECT.ToUpper() : subject.ToUpper())
                                        && q.QUOTE_STAGE_ID == (quoteStage_ID == null ? q.QUOTE_STAGE_ID : quoteStage_ID)
                                        && q.ASSIGN_TO_ID == (assignUser_ID == null ? q.ASSIGN_TO_ID : assignUser_ID)
                                        && (q.DATE_CREATED >= dateFrom && q.DATE_CREATED < dateTo)
                                    select q;
                        List<QUOTE_DETAIL> orgs = Query.AsNoTracking().ToList();
                        sum.AddRange(orgs);
                    }
                }

                if (sum.Count > 0)
                    return sum;
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Get All Quote Stage
        public List<QUOTE_STAGE> GetAllQuoteStage()
        {
            try
            {
                var Query = from q in QUOTE_STAGE
                            select q;
                List<QUOTE_STAGE> qs = Query.ToList();
                if (qs.Count > 0)
                    return qs;
                return null;
            }
            catch
            {
                return null;
            }
        }
        //Add new quote
        public int AddNewQuote(QUOTE quote, ITEM item, List<ITEM_DETAIL> itemDetails)
        {
            var trans = this.Database.BeginTransaction();
            try
            {
                this.ITEMS.Add(item);
                this.SaveChanges();
                foreach (ITEM_DETAIL idetail in itemDetails)
                {
                    idetail.ITEM_ID = item.ID;
                    this.ITEM_DETAILS.Add(idetail);
                    this.SaveChanges();
                }
                quote.ITEM_ID = item.ID;
                this.QUOTES.Add(quote);
                this.SaveChanges();
                trans.Commit();
                return 1;
            }
            catch
            {
                trans.Rollback();
                return -1;
            }
        }
        //Update Invoice
        public int UpdateQuote(QUOTE quote, ITEM item, List<ITEM_DETAIL> itemDetails)
        {
            var trans = this.Database.BeginTransaction();
            try
            {
                var original1 = ITEMS.Find(item.ID);
                if (original1 != null)
                {
                    Entry(original1).CurrentValues.SetValues(item);
                }
                else
                    ITEMS.Add(item);
                SaveChanges();
                var deleteQuery = from i in ITEM_DETAILS
                                  where i.ITEM_ID == item.ID
                                  select i;
                ITEM_DETAILS.RemoveRange(deleteQuery.ToList());
                foreach (ITEM_DETAIL i in itemDetails)
                {
                    i.ITEM_ID = item.ID;
                    this.ITEM_DETAILS.Add(i);
                }
                this.SaveChanges();
                var original3 = QUOTES.Find(quote.ID);
                if (original3 != null)
                {
                    quote.ITEM_ID = item.ID;
                    Entry(original3).CurrentValues.SetValues(quote);
                    SaveChanges();
                }
                trans.Commit();
                return 1;
            }
            catch
            {
                trans.Rollback();
                return -1;
            }
        }
    }
}
